import random
import pymysql
import string


class BankDatabase:
    def __init__(self, host, port, user, password, database):
        self.db = pymysql.connect(host=host, port=port, user=user, password=password)
        self.cur = self.db.cursor()
        self.cur.execute(f"USE {database}")
        self.business = ["取款", "存款", "挂失", "还贷"]

    def query_user(self, user_id):
        sql = "SELECT * FROM bank_user WHERE user_id = %s"
        self.cur.execute(sql, user_id)
        result = self.cur.fetchone()
        return result

    def query_bank_cards(self, card_id, password):
        sql = "SELECT * FROM bank_card WHERE card_id = %s and password = %s"
        self.cur.execute(sql, (card_id, password))
        result = self.cur.fetchone()
        return result

    def add_card(self, rand_card_id, password, user_id=1):
        # digits = string.digits
        # rand_card_id = ''.join(random.choices(digits, k=16))
        sql = "INSERT INTO bank_card(card_id, user_id, password, card_state, balance) VALUES(%s, %s, %s, %s, %s)"
        result = self.cur.execute(sql, (rand_card_id, user_id, password, 0, 0))
        self.db.commit()
        if result:
            return rand_card_id
        return result

    def insert_deal(self, card_id, deal_content, deal_time, deal_money, notes):
        sql = "INSERT INTO bank_deal (card_id, deal_content, deal_time, deal_money, notes) VALUES (%s, %s, %s, %s, %s)"
        values = (card_id, deal_content, deal_time, deal_money, notes)
        try:
            self.cur.execute(sql, values)
            self.db.commit()
            return True  # 插入成功
        except Exception as e:
            print("Error inserting deal:", e)
            self.db.rollback()  # 发生异常时回滚事务
            return False  # 插入失败

    def query_all_deal(self):
        sql = "SELECT * FROM bank_deal"
        self.cur.execute(sql)
        result = self.cur.fetchall()
        return result
    def test(self):
        sql = "desc bank_deal"
        self.cur.execute(sql)
        result = self.cur.fetchall()
        return result
    def close(self):
        self.cur.close()
        self.db.close()
